【アップデート】BigQueryで再帰クエリのRECURSIVEがGAになりました

初めに

データアナリティクス事業本部でGoogle Cloudのデータエンジニアをしています、はんざわです。
2023年3月2日にBigQueryのWITH RECURSIVEがGAになりました。

https://cloud.google.com/bigquery/docs/release-notes#March_02_2023

WITH RECURSIVEを使うことでWITH句で再帰することが可能になりました。

【英語版ドキュメント】

試してみる

WITH RECURSIVE
  T1 AS ( (SELECT 1 AS n) UNION ALL (SELECT n + 1 AS n FROM T1 WHERE n < 3) )
SELECT n FROM T1

+---+
| n |
+---+
| 2 |
| 1 |
| 3 |
+---+

上のクエリは公式ドキュメントに記載されている再帰クエリの例です。
この例では初期値1のnが3になるまでnに1ずつ加算されます。

現在、再帰回数は500回が上限になっており、それを超える場合はクエリが失敗します。

WITH RECURSIVE
  T1 AS ( (SELECT 1 AS n) UNION ALL (SELECT n + 1 AS n FROM T1 WHERE n < 600) )
SELECT n FROM T1 ORDER BY n ASC

A recursive CTE has reached the maximum number of iterations: 500.

他にも以下のようなケースには対応していないようです。

• FROM句外で再帰クエリを参照できない
• 再帰クエリ内でORDER BYとLIMITは使えない
• 再帰クエリ内で集計関数は使えない
• 再帰クエリ内でウィンドウ関数は使えない
• 再帰クエリ内でDISTINCTとGROUP BYは使えない

詳しくは以下のドキュメントを参考にしてください。

【規則と制約】

おまけ

再帰関数といえばフィボナッチ数列ということでSQLでフィボナッチ数列を書いてみました。

DECLARE num INT64;
set num = 10
;

WITH RECURSIVE TMP AS (
  SELECT
    0 AS a, 1 AS b, 1 AS cnt
  UNION ALL
  SELECT
    b AS a, a + b AS b, cnt + 1 AS cnt
  FROM TMP WHERE cnt < num
)

SELECT b AS Fibonacci_Number
FROM TMP WHERE cnt = num
ORDER BY cnt ASC

+------------------+
| Fibonacci_Number |
+------------------+
|        55        |
+------------------+

set num = 10の部分で何番目のフィボナッチ数を求めたいか指定します。

再帰句で上の画像のようなテーブルを作成し、サブクエリを呼び出す際にWHERE句で対象のレコードを呼び出しています。

DECLARE num INT64;
set num = 100
;

WITH RECURSIVE TMP AS (
  SELECT
    0 AS a, 1 AS b, 1 AS cnt
  UNION ALL
  SELECT
    b AS a, a + b AS b, cnt + 1 AS cnt
  FROM TMP WHERE cnt < num
)

SELECT b AS Fibonacci_Number
FROM TMP WHERE cnt = num
ORDER BY cnt ASC

int64 overflow: 4660046610375530309 + 7540113804746346429

数値が大きくなりすぎるとオーバーフローするので気をつけましょう

まとめ

新しくGAになったRECURSIVEを紹介しました。
これによりデータ処理の幅がさらに広がると思いますので是非試してみてください。